package com.nccsjz.back.resources.dao;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.nccsjz.pojo.Resources;
import com.nccsjz.utils.DateUtil;
import com.nccsjz.utils.Pager;
import com.nccsjz.base.BaseDAO;

public class ResourcesDAO extends BaseDAO {
	boolean flag = false;

	public ResourcesDAO(Connection con) {
		super(con);
	}

	// add resources
	public boolean addResources(Resources res) throws SQLException {

		try {
			String sql = "insert into resources (resourcetitle,resourcepath,uploaddate) values(?,?,?)";
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, res.getResourcetitle());
			pstmt.setString(2, res.getResourcepath());
			pstmt.setDate(3, DateUtil.convertDate(new java.util.Date()));
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// delete resources
	public boolean deleteResources(Long id) throws SQLException {
		try {
			String sql = "delete from resources where resourceid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			int num = pstmt.executeUpdate();
			if (num != 0) {
				flag = true;
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		return flag;
	}

	// batch delete resources
	public boolean deleteBatchResources(List<Long> list) throws SQLException {
		int num[] = null;
		try {
			String sql = "delete from resources where resourceid=?";
			pstmt = con.prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				pstmt.setLong(1, list.get(i));
				pstmt.addBatch();
			}
			num = pstmt.executeBatch();
			if (num != null) {
				flag = true;
			}

		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);

		}
		return flag;
	}

	// get one resources
	public Resources getReources(Long id) throws SQLException {
		Resources res = null;
		try {
			String sql = "select * from resources where resourceid=?";
			pstmt = con.prepareStatement(sql);
			pstmt.setLong(1, id);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				res = new Resources();
				res.setResourceid(rs.getLong("resourceid"));
				res.setResourcetitle(rs.getString("resourcetitle"));
				res.setResourcepath(rs.getString("resourcepath"));
				res.setUploaddate(rs.getDate("uploaddate"));
			}
		} catch (Exception ex) {
			ex.printStackTrace();

		} finally {
			closeAll(rs, pstmt);
		}
		return res;
	}

	// pager display resources
	public Pager getResources(int pageSize, int pageNo) throws SQLException {
		List<Resources> list = new ArrayList<Resources>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from resources";
			// String
			// sql2="select * from(select u.*,rownum rn from(select * from resources order by uploaddate desc) u where rownum<=?)where rn>?";
			String sql2 = "select * from (select resources.*, row_number() over(order by resourceid desc) rn "
					+ "from resources) u where u.rn > ? and u.rn <= ?";
			pstmt = con.prepareStatement(sql1);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			pstmt.setInt(1, (pageNo - 1) * pageSize);
			pstmt.setInt(2, pageNo * pageSize);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Resources res = new Resources();
				res.setResourceid(rs.getLong("resourceid"));
				res.setResourcetitle(rs.getString("resourcetitle"));
				res.setResourcepath(rs.getString("resourcepath"));
				res.setUploaddate(rs.getDate("uploaddate"));
				list.add(res);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}

	// select resources by title
	public Pager getResources(int pageSize, int pageNo, String title)
			throws SQLException {
		List<Resources> list = new ArrayList<Resources>();
		int rownum = 0;
		try {
			String sql1 = "select count(*) from resources where resourcetitle like ?";
			// String
			// sql2="select * from(select u.*,rownum rn from(select * from resources  where resourcetitle like ? order by uploaddate desc) u where rownum<=?)where rn>?";
			String sql2 = "select * from (select resources.*, row_number() over(order by resourceid desc) rn from resources ";
			boolean flag = StringUtils.isNotBlank(title);
			String ctitle = null;
			if (flag) {
				ctitle = "%" + title + "%";
				sql1 += " where resourcetitle like ? ";
				sql2 += " where resourcetitle like ? ";
			}
			pstmt = con.prepareStatement(sql1);
			if (flag) {
				pstmt.setString(1, ctitle);
			}
			rs = pstmt.executeQuery();
			if (rs.next()) {
				rownum = rs.getInt(1);
			}
			pstmt = con.prepareStatement(sql2);
			if(flag){
				pstmt.setString(1, ctitle);
				pstmt.setInt(2, (pageNo - 1) * pageSize);
				pstmt.setInt(3, pageNo * pageSize);				
			}else{
				pstmt.setInt(1, (pageNo - 1) * pageSize);
				pstmt.setInt(2, pageNo * pageSize);
			}			
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Resources res = new Resources();
				res.setResourceid(rs.getLong("resourceid"));
				res.setResourcetitle(rs.getString("resourcetitle"));
				res.setResourcepath(rs.getString("resourcepath"));
				res.setUploaddate(rs.getDate("uploaddate"));
				list.add(res);
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			closeAll(rs, pstmt);
		}
		Pager pager = new Pager(pageSize, pageNo, rownum, list);
		return pager;
	}
}
